import datetime
import json

import cx_Oracle
import numpy as np

"""查询料肉比标准值"""

user = "pigpos"
passwd = "P1g#2023pos"
# listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
listener = '10.240.24.135/NMDCFARM'
# listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
print_sql_flag = 0


def select(sql) -> json:
    if print_sql_flag == 1:
        print(sql)
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


def get_fcr(m, n):
    weigh_in = str(m + 5)
    weigh_out = str(n + 6)
    sql = "select t.fcr frOm FR_MAS_SWINE_FCR_INF t where project_code='SW' and t.weight_in = '{}' and t.weight_out = '{}'".format(
        weigh_in, weigh_out)
    list = select(sql)
    list = json.loads(list)
    fcr = 0
    if len(list) > 0:
        a = list[0]
        fcr = a['FCR']
    print('fcr进【%s】出【%s】=%s' % (weigh_in, weigh_out, fcr))
    return fcr


if __name__ == '__main__':
    print('开始查询')

    # s = np.zeros((136, 185), float)
    s = np.zeros((3, 3), float)

    for index_m, m in enumerate(s):
        # print(m)
        # print(index_m)
        for index_n, n in enumerate(m):
            fcr = get_fcr(index_m, index_n)
            s[index_m][index_n] = fcr

    print(s)

    # 打开文件
    file = open("C:/Users/yangjianzhang/Desktop/test.txt", "w")
    # 将二维数组转换为字典形式
    data = s.tolist()
    # 将字典转换为JSON格式
    json_str = json.dumps(data)
    file.write(str(json_str))
    # 关闭文件
    file.close()
